#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
数据库管理模块
"""

import sqlite3
import os
from config import DATABASE_PATH

class DatabaseManager:
    def __init__(self):
        """初始化数据库管理器"""
        self.init_database()
    
    def init_database(self):
        """初始化数据库表结构"""
        try:
            conn = sqlite3.connect(DATABASE_PATH)
            cursor = conn.cursor()
            
            # 创建方案表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS plan (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name VARCHAR(50) NOT NULL,
                    type VARCHAR(20) NOT NULL,
                    is_active BOOLEAN DEFAULT 1
                )
            """)
            
            # 创建铃声时间点表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS bell_time (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    plan_id INTEGER NOT NULL,
                    time TIME NOT NULL,
                    bell_type VARCHAR(20) NOT NULL,
                    audio_file VARCHAR(100) NOT NULL,
                    volume INTEGER DEFAULT 80,
                    FOREIGN KEY (plan_id) REFERENCES plan (id)
                )
            """)
            
            # 创建音频文件表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS audio_files (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    filename VARCHAR(100) NOT NULL,
                    path VARCHAR(200) NOT NULL,
                    duration INTEGER DEFAULT 0,
                    upload_time DATETIME DEFAULT CURRENT_TIMESTAMP
                )
            """)
            
            # 创建星期方案设置表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS weekday_plan (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    weekday VARCHAR(10) NOT NULL UNIQUE,  -- monday, tuesday, etc.
                    plan_id INTEGER,
                    FOREIGN KEY (plan_id) REFERENCES plan (id)
                )
            """)
            
            conn.commit()
            conn.close()
            return True
        except Exception as e:
            print(f"初始化数据库时出错: {e}")
            return False
    
    def add_plan(self, name, type, is_active=True):
        """
        添加方案
        
        Args:
            name (str): 方案名称
            type (str): 方案类型
            is_active (bool): 是否启用
            
        Returns:
            int: 新增记录的ID
        """
        try:
            conn = sqlite3.connect(DATABASE_PATH)
            cursor = conn.cursor()
            
            cursor.execute("""
                INSERT INTO plan (name, type, is_active)
                VALUES (?, ?, ?)
            """, (name, type, is_active))
            
            schedule_id = cursor.lastrowid
            conn.commit()
            conn.close()
            return schedule_id if schedule_id is not None else -1
        except Exception as e:
            print(f"添加方案时出错: {e}")
            return -1
    
    def add_bell_time(self, plan_id, time, bell_type, audio_file, volume=80):
        """
        添加铃声时间点
        
        Args:
            plan_id (int): 方案ID
            time (str): 播放时间(HH:MM:SS格式)
            bell_type (str): 铃声类型
            audio_file (str): 音频文件路径
            volume (int): 音量大小(0-100)
            
        Returns:
            int: 新增记录的ID
        """
        try:
            conn = sqlite3.connect(DATABASE_PATH)
            cursor = conn.cursor()
            
            cursor.execute("""
                INSERT INTO bell_time (plan_id, time, bell_type, audio_file, volume)
                VALUES (?, ?, ?, ?, ?)
            """, (plan_id, time, bell_type, audio_file, volume))
            
            bell_time_id = cursor.lastrowid
            conn.commit()
            conn.close()
            return bell_time_id if bell_time_id is not None else -1
        except Exception as e:
            print(f"添加铃声时间点时出错: {e}")
            return -1
    
    def add_audio_file(self, filename, path, duration=0):
        """
        添加音频文件记录
        
        Args:
            filename (str): 文件名
            path (str): 文件路径
            duration (int): 时长(秒)
            
        Returns:
            int: 新增记录的ID
        """
        try:
            conn = sqlite3.connect(DATABASE_PATH)
            cursor = conn.cursor()
            
            cursor.execute("""
                INSERT INTO audio_files (filename, path, duration)
                VALUES (?, ?, ?)
            """, (filename, path, duration))
            
            audio_id = cursor.lastrowid
            conn.commit()
            conn.close()
            return audio_id if audio_id is not None else -1
        except Exception as e:
            print(f"添加音频文件记录时出错: {e}")
            return -1
    
    def get_active_plans(self):
        """
        获取启用的方案
        
        Returns:
            list: 方案列表
        """
        try:
            conn = sqlite3.connect(DATABASE_PATH)
            cursor = conn.cursor()
            
            cursor.execute("""
                SELECT id, name, type FROM plan
                WHERE is_active = 1
            """)
            
            schedules = cursor.fetchall()
            conn.close()
            return schedules
        except Exception as e:
            print(f"获取方案时出错: {e}")
            return []
    
    def get_bell_times_by_plan(self, plan_id):
        """
        根据方案ID获取铃声时间点
        
        Args:
            plan_id (int): 方案ID
            
        Returns:
            list: 铃声时间点列表
        """
        try:
            conn = sqlite3.connect(DATABASE_PATH)
            cursor = conn.cursor()
            
            cursor.execute("""
                SELECT time, bell_type, audio_file, volume FROM bell_time
                WHERE plan_id = ?
                ORDER BY time
            """, (plan_id,))
            
            bell_times = cursor.fetchall()
            conn.close()
            return bell_times
        except Exception as e:
            print(f"获取铃声时间点时出错: {e}")
            return []
    
    def get_all_audio_files(self):
        """
        获取所有音频文件记录
        
        Returns:
            list: 音频文件列表
        """
        try:
            conn = sqlite3.connect(DATABASE_PATH)
            cursor = conn.cursor()
            
            cursor.execute("""
                SELECT id, filename, path, duration, upload_time FROM audio_files
                ORDER BY upload_time DESC
            """)
            
            audio_files = cursor.fetchall()
            conn.close()
            return audio_files
        except Exception as e:
            print(f"获取音频文件记录时出错: {e}")
            return []
    
    def update_plan(self, plan_id, name, type, is_active=True):
        """
        更新方案
        
        Args:
            plan_id (int): 方案ID
            name (str): 方案名称
            type (str): 方案类型
            is_active (bool): 是否启用
            
        Returns:
            bool: 是否更新成功
        """
        try:
            conn = sqlite3.connect(DATABASE_PATH)
            cursor = conn.cursor()
            
            cursor.execute("""
                UPDATE plan SET name = ?, type = ?, is_active = ? WHERE id = ?
            """, (name, type, is_active, plan_id))
            
            conn.commit()
            conn.close()
            return True
        except Exception as e:
            print(f"更新方案时出错: {e}")
            return False
    
    def update_bell_time(self, plan_id, old_time, new_time, bell_type, audio_file, volume=80):
        """
        更新铃声时间点
        
        Args:
            plan_id (int): 方案ID
            old_time (str): 原时间(HH:MM:SS格式)
            new_time (str): 新时间(HH:MM:SS格式)
            bell_type (str): 铃声类型
            audio_file (str): 音频文件路径
            volume (int): 音量大小(0-100)
            
        Returns:
            bool: 是否更新成功
        """
        try:
            conn = sqlite3.connect(DATABASE_PATH)
            cursor = conn.cursor()
            
            cursor.execute("""
                UPDATE bell_time 
                SET time = ?, bell_type = ?, audio_file = ?, volume = ?
                WHERE plan_id = ? AND time = ?
            """, (new_time, bell_type, audio_file, volume, plan_id, old_time))
            
            conn.commit()
            conn.close()
            return True
        except Exception as e:
            print(f"更新铃声时间点时出错: {e}")
            return False
    
    def delete_plan(self, plan_id):
        """
        删除方案（包括相关的铃声时间点）
        
        Args:
            plan_id (int): 方案ID
            
        Returns:
            bool: 是否删除成功
        """
        conn = None
        try:
            conn = sqlite3.connect(DATABASE_PATH)
            cursor = conn.cursor()
            
            # 开始事务
            cursor.execute("BEGIN TRANSACTION")
            
            # 先删除相关的铃声时间点
            cursor.execute("""
                DELETE FROM bell_time WHERE plan_id = ?
            """, (plan_id,))
            
            # 再删除方案
            cursor.execute("""
                DELETE FROM plan WHERE id = ?
            """, (plan_id,))
            
            # 检查是否成功删除了方案
            if cursor.rowcount == 0:
                # 如果没有删除任何行，回滚事务
                conn.rollback()
                conn.close()
                return False
            
            # 提交事务
            conn.commit()
            conn.close()
            return True
        except Exception as e:
            print(f"删除方案时出错: {e}")
            # 回滚事务
            if conn:
                try:
                    conn.rollback()
                except:
                    pass
                conn.close()
            return False
    
    def delete_bell_times_by_plan(self, plan_id):
        """
        删除方案的所有铃声时间点
        
        Args:
            plan_id (int): 方案ID
            
        Returns:
            bool: 是否删除成功
        """
        try:
            conn = sqlite3.connect(DATABASE_PATH)
            cursor = conn.cursor()
            
            cursor.execute("""
                DELETE FROM bell_time WHERE plan_id = ?
            """, (plan_id,))
            
            conn.commit()
            conn.close()
            return True
        except Exception as e:
            print(f"删除方案的铃声时间点时出错: {e}")
            return False
    
    def delete_audio_file(self, audio_id):
        """
        删除音频文件记录
        
        Args:
            audio_id (int): 音频文件ID
            
        Returns:
            bool: 是否删除成功
        """
        try:
            conn = sqlite3.connect(DATABASE_PATH)
            cursor = conn.cursor()
            
            cursor.execute("""
                DELETE FROM audio_files WHERE id = ?
            """, (audio_id,))
            
            conn.commit()
            conn.close()
            return True
        except Exception as e:
            print(f"删除音频文件时出错: {e}")
            return False
    
    def save_weekday_plan(self, weekday, plan_id):
        """
        保存星期方案设置
        
        Args:
            weekday (str): 星期几 (monday, tuesday, etc.)
            plan_id (int): 方案ID
            
        Returns:
            bool: 是否保存成功
        """
        try:
            conn = sqlite3.connect(DATABASE_PATH)
            cursor = conn.cursor()
            
            # 先尝试更新 existing record
            cursor.execute("""
                UPDATE weekday_plan SET plan_id = ? WHERE weekday = ?
            """, (plan_id, weekday))
            
            # 如果没有更新任何记录，则插入新记录
            if cursor.rowcount == 0:
                cursor.execute("""
                    INSERT INTO weekday_plan (weekday, plan_id) VALUES (?, ?)
                """, (weekday, plan_id))
            
            conn.commit()
            conn.close()
            return True
        except Exception as e:
            print(f"保存星期方案设置时出错: {e}")
            return False
    
    def get_weekday_plan(self, weekday):
        """
        获取星期方案设置
        
        Args:
            weekday (str): 星期几 (monday, tuesday, etc.)
            
        Returns:
            int: 方案ID，如果未设置则返回None
        """
        try:
            conn = sqlite3.connect(DATABASE_PATH)
            cursor = conn.cursor()
            
            cursor.execute("""
                SELECT plan_id FROM weekday_plan WHERE weekday = ?
            """, (weekday,))
            
            result = cursor.fetchone()
            conn.close()
            
            return result[0] if result else None
        except Exception as e:
            print(f"获取星期方案设置时出错: {e}")
            return None
    
    def get_all_weekday_plans(self):
        """
        获取所有星期方案设置
        
        Returns:
            dict: 星期方案映射 {weekday: plan_id}
        """
        try:
            conn = sqlite3.connect(DATABASE_PATH)
            cursor = conn.cursor()
            
            cursor.execute("""
                SELECT weekday, plan_id FROM weekday_plan
            """)
            
            results = cursor.fetchall()
            conn.close()
            
            return {row[0]: row[1] for row in results}
        except Exception as e:
            print(f"获取所有星期方案设置时出错: {e}")
            return {}

def main():
    # 测试代码
    db_manager = DatabaseManager()
    print("数据库管理模块测试")
    
    # 添加测试数据
    plan_id = db_manager.add_plan("工作日方案", "weekday")
    if plan_id > 0:
        print(f"成功添加方案，ID: {plan_id}")
        
        # 添加铃声时间点
        bell_time_id = db_manager.add_bell_time(
            plan_id, "08:00:00", "上课铃", "audio/class_start.mp3", 80
        )
        if bell_time_id > 0:
            print(f"成功添加铃声时间点，ID: {bell_time_id}")
    
    # 添加音频文件记录
    audio_id = db_manager.add_audio_file("class_start.mp3", "audio/class_start.mp3", 10)
    if audio_id > 0:
        print(f"成功添加音频文件记录，ID: {audio_id}")

if __name__ == "__main__":
    main()